# -*- coding: utf-8 -*-

import logging
import os
import re
import time
import datetime
import config

from scrapy.crawler import CrawlerProcess
from django.core.management.base import BaseCommand, CommandError
from scrapy.utils.log import configure_logging
from scrapy.utils.project import get_project_settings
from socar import settings
from sqlhelper import SqlHelper
from tools.decrypt_autohome_bbs import decrypt_autohome_bbs
from tools.parse_autohome_bbs import parse_autohome_bbs


# 入库数据，入库之后，更新相应字段
# python manage.py storage_bbs
class Command(BaseCommand):
    help = 'decrypt storage bbs'

    def add_arguments(self, parser):
        parser.add_argument('-a', action = 'append', dest = 'spargs', default = [],
                            help = 'set spider argument (may be repeated)')

    # 必须实现的方法
    def handle(self, *args, **options):
        os.chdir(settings.BASE_DIR)
        spargs = arglist_to_dict(options['spargs']) or {}

        # 准备同意词替换字典
        self.online_sql = SqlHelper(db_name = config.carvoice_autohome_database_name,
                                    db_config = config.online_database_config)
        command = '''SELECT syn_word,syn_syn FROM `topic_syn`'''

        result = self.online_sql.query(command = command, cursor_type = 'dict')
        topic_syn = {}
        for res in result:
            topic_syn[res.get('syn_word')] = res.get('syn_syn')

        # 镜像数据库
        self.mirror_sql = SqlHelper()
        # 入库数据库
        self.online_sql.select_db(config.online_database_name)

        # syn 数据库
        self.syn_sql = SqlHelper(config.syn_database_name, config.online_database_config)

        command = '''SELECT * FROM {table_name} WHERE (`decrypt_status`=1 or request_status=404)'''.format(
            table_name = config.bbs_mirror_temp_table_name)

        result = self.mirror_sql.query(command, cursor_type = 'dict')
        for res in result:
            print('id：%s forum_id:%s car_id:%s status:%s' % (
                res.get('id'), res.get('forum_id'), res.get('car_id'), res.get('request_status')))

            # 入库
            if res.get('request_status') == 200 and res.get('conmain') != None:
                data = parse_autohome_bbs(res.get('conmain'))

                url = res.get('url')
                strs = url.split('-')
                abc = strs[1] if len(strs) > 1 else 0

                table_name = 'bbs_%s_%s' % (abc, str(res.get('car_id')))
                self.create_table_bbs(table_name, self.online_sql)

                data['bbs_bbsid'] = res.get('forum_id')
                data['bbs_cid'] = res.get('car_id')

                # 插入入库数据库
                self.online_sql.insert_json(data, table_name)

                # 进行 syn 处理
                try:
                    bbs_content = data.get('bbs_content')
                    bbs_content = replace_re(bbs_content)
                    bbs_content = syn(bbs_content, topic_syn)
                    data['bbs_content'] = bbs_content

                    bbs_title = data.get('bbs_title')
                    bbs_title = replace_re(bbs_title)
                    bbs_title = syn(bbs_title, topic_syn)
                    data['bbs_title'] = bbs_title
                except:
                    pass

                self.create_table_syn(table_name, self.syn_sql)
                del data['bbs_state']

                # 插入 syn 数据库
                self.syn_sql.insert_json(data, table_name)

                # 做增量计算 只计算 bbs_c 的所有文章
                if 'bbs_c_' in table_name:
                    self.mirror_sql.insert_json(data, 'bbs_mirror')

            # 插入镜像数据表
            res['storage_status'] = 1
            res['car_id'] = res.get('car_id')
            table_name = 'bbs_mirror_%s' % str(int(int(res.get('forum_id')) / 10000000))
            self.mirror_sql.insert_json(res, table_name = table_name)

            # 删除临时表中的内容
            command = 'DELETE FROM {table_name} WHERE id={id}'.format(
                table_name = config.bbs_mirror_temp_table_name, id = res.get('id'))
            self.mirror_sql.execute(command)
        self.online_sql.commit()

    def create_table_bbs(self, table_name, sql):
        if sql.check_table_exists(table_name) == False:
            command = '''
              CREATE TABLE  IF NOT EXISTS `{table_name}` (
              `bbs_bbsid` int(11) NOT NULL DEFAULT '0' COMMENT '帖子ID',
              `bbs_cid` varchar(200) DEFAULT NULL COMMENT '车型ID',
              `bbs_title` longtext COMMENT '标题',
              `bbs_qa` tinyint(4) DEFAULT '0' COMMENT '是否问答贴',
              `bbs_jing` tinyint(4) DEFAULT '0' COMMENT '是否精华帖',
              `bbs_content` longtext COMMENT '帖子内容',
              `bbs_ptime` datetime DEFAULT NULL COMMENT '发帖时间',
              `bbs_djs` int(11) DEFAULT NULL COMMENT '点击数',
              `bbs_hfs` int(11) DEFAULT NULL COMMENT '回复数',
              `bbs_uid` int(11) DEFAULT NULL COMMENT '用户ID',
              `bbs_yhmc` varchar(255) DEFAULT NULL COMMENT '用户姓名',
              `bbs_jhs` int(11) DEFAULT NULL COMMENT '用户发帖的精华帖子数',
              `bbs_yhfts` int(11) DEFAULT NULL COMMENT '用户发帖数',
              `bbs_yhhts` int(11) DEFAULT NULL COMMENT '用户回帖数量',
              `bbs_zcsj` date DEFAULT NULL COMMENT '注册日期',
              `bbs_sf` varchar(50) DEFAULT '' COMMENT '省份',
              `bbs_cs` varchar(50) DEFAULT '' COMMENT '地市',
              `bbs_khd` varchar(200) DEFAULT NULL COMMENT '来自于哪个客户端',
              `bbs_rz` tinyint(5) DEFAULT NULL COMMENT '检查是否为认证车主，具体的数据在qczj_user_rz表',
              `bbs_jxlx` int(11) DEFAULT NULL COMMENT '属于论坛精选日报的何种类型',
              `bbs_byt` tinyint(5) DEFAULT NULL COMMENT '是否为抱怨贴',
              `bbs_state` tinyint(4) DEFAULT '0' COMMENT '解码状态',
              `bbs_uptime` datetime DEFAULT NULL COMMENT '更新时间',
              PRIMARY KEY (`bbs_bbsid`),
              KEY `bbs_qa` (`bbs_qa`,`bbs_jing`),
              KEY `bbs_bt` (`bbs_title`(250)),
              KEY `bbs_zcrq` (`bbs_zcsj`),
              KEY `bbs_state` (`bbs_state`)
            ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
            '''.format(table_name = table_name)

            sql.create_table(command)

    def create_table_syn(self, table_name, sql):
        if sql.check_table_exists(table_name) == False:
            command = '''
                CREATE TABLE `{table_name}` (
              `bbs_bbsid` int(11) NOT NULL DEFAULT '0' COMMENT '帖子ID',
              `bbs_cid` varchar(200) DEFAULT NULL COMMENT '车型ID',
              `bbs_title` longtext COMMENT '标题',
              `bbs_qa` tinyint(4) DEFAULT '0' COMMENT '是否问答贴',
              `bbs_jing` tinyint(4) DEFAULT '0' COMMENT '是否精华帖',
              `bbs_content` longtext COMMENT '帖子内容',
              `bbs_ptime` datetime DEFAULT NULL COMMENT '发帖时间',
              `bbs_djs` int(11) DEFAULT NULL COMMENT '点击数',
              `bbs_hfs` int(11) DEFAULT NULL COMMENT '回复数',
              `bbs_uid` int(11) DEFAULT NULL COMMENT '用户ID',
              `bbs_yhmc` varchar(255) DEFAULT NULL COMMENT '用户姓名',
              `bbs_jhs` int(11) DEFAULT NULL COMMENT '用户发帖的精华帖子数',
              `bbs_yhfts` int(11) DEFAULT NULL COMMENT '用户发帖数',
              `bbs_yhhts` int(11) DEFAULT NULL COMMENT '用户回帖数量',
              `bbs_zcsj` date DEFAULT NULL COMMENT '注册日期',
              `bbs_sf` varchar(50) DEFAULT '' COMMENT '省份',
              `bbs_cs` varchar(50) DEFAULT '' COMMENT '地市',
              `bbs_khd` varchar(200) DEFAULT NULL COMMENT '来自于哪个客户端',
              `bbs_rz` tinyint(5) DEFAULT NULL COMMENT '检查是否为认证车主，具体的数据在qczj_user_rz表',
              `bbs_jxlx` int(11) DEFAULT NULL COMMENT '属于论坛精选日报的何种类型',
              `bbs_byt` tinyint(5) DEFAULT NULL COMMENT '是否为抱怨贴',
              `bbs_uptime` datetime DEFAULT NULL COMMENT '更新时间',
              PRIMARY KEY (`bbs_bbsid`),
              KEY `bbs_qa` (`bbs_qa`,`bbs_jing`),
              KEY `bbs_bt` (`bbs_title`(250)),
              KEY `bbs_zcrq` (`bbs_zcsj`)
            ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
            '''.format(table_name = table_name)

            sql.create_table(command)


# 进行五次正则替换
def replace_re(input):
    # 第一个参数是正则表达式，第二个参数是要替换成的内容，第三个参数是替换原字符串
    output = re.sub("[，。！!；,;]{1,}", "}{", input)
    output1 = re.sub("[ �　\s+]", "", output)
    output2 = re.sub(
        "(http://|ftp://|https://|www){0,1}[^\u4e00-\u9fa5\\s]*?\\.(com|net|cn|me|tw|fr)[^\u4e00-\u9fa5\\s]*", "",
        output1).upper()
    output3 = re.sub("\\?{1,}|？{1,}", "?", output2)
    output4 = re.sub("[\\. ~\\-\\+\\*><]{2,}", "}{", output3)
    output5 = re.sub("\n{1,}", "\n", output4)
    return output5


# 进行同义词，替换
def syn(text, idict):
    rx = re.compile('|'.join(map(re.escape, idict)))

    def one_xlat(match):
        return idict[match.group(0)]

    return rx.sub(one_xlat, text)


def arglist_to_dict(arglist):
    """Convert a list of arguments like ['arg1=val1', 'arg2=val2', ...] to a
    dict
    """
    return dict(x.split('=', 1) for x in arglist)
